﻿Module ResourceDao

    Public Sub insert(ByRef resource As Resource)
        DataAccessConnection.openConnection()
        Dim command As New OracleClient.OracleCommand

        Dim query As String = ""

        query = "INSERT INTO " & resource.getTableName & " (firstname, surname, document_type, "
        query &= "document_number, email, create_date, hour_cost)"
        query &= " values('" & resource.firstnameProperty & "'"
        query &= ", '" & resource.surnameProperty & "'"
        query &= ", '" & resource.documentTypeProperty & "'"
        query &= ", '" & resource.documentNumberProperty & "'"
        query &= ", '" & resource.emailProperty & "'"
        query &= ", '" & Today.Date & "'"
        query &= ", '" & resource.hourCostProperty & "' )"

        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = query

        command.ExecuteNonQuery()

        DataAccessConnection.closeConnection()
    End Sub

    Public Sub update(ByRef resource As Resource)
        DataAccessConnection.openConnection()
        Dim command As New OracleClient.OracleCommand

        Dim query As String = ""

        query = "UPDATE " & resource.getTableName & ""
        query &= " SET firstname = '" & resource.firstnameProperty & "' , "
        query &= " surname = '" & resource.surnameProperty & "' , "
        query &= " email = '" & resource.emailProperty & "' , "
        query &= " hour_cost = '" & resource.hourCostProperty & "'"
        query &= " WHERE id = " & resource.idProperty & ""

        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = query

        command.ExecuteNonQuery()

        DataAccessConnection.closeConnection()
    End Sub

    Public Function searchDataByFilter(ByRef resource As Resource) As Data.DataTable
        DataAccessConnection.openConnection()

        Dim command As New OracleClient.OracleCommand
        Dim table As New Data.DataTable

        Dim query As String = ""

        query = "SELECT * FROM " & resource.getTableName & " WHERE "
        query &= " firstname like '%" & resource.firstnameProperty & "%'"
        query &= " AND surname like '%" & resource.surnameProperty & "%'"
        query &= " AND (document_type = " & resource.documentTypeProperty & " OR " & resource.documentTypeProperty & " = 0)"
        query &= " AND (document_number = " & resource.documentNumberProperty & " OR " & resource.documentNumberProperty & " = 0)"
        query &= " AND (id = " & resource.idProperty & " OR " & resource.idProperty & " = 0)"
        query &= " AND end_date IS NULL"

        command.CommandType = CommandType.Text
        command.CommandText = query
        command.Connection = connection

        table.Load(command.ExecuteReader)

        DataAccessConnection.closeConnection()

        Return table
    End Function

    Public Function searchData(ByRef resource As Resource, ByVal stringSelect As String) As Data.DataTable
        DataAccessConnection.openConnection()

        Dim command As New OracleClient.OracleCommand
        Dim table As New Data.DataTable

        command.CommandType = CommandType.Text
        command.CommandText = "SELECT " & stringSelect & " FROM " & resource.getTableName & " WHERE end_date IS NULL"

        command.Connection = connection

        table.Load(command.ExecuteReader)

        DataAccessConnection.closeConnection()

        Return table
    End Function

    Public Function searchDataById(ByRef obj As IObject, ByVal stringSelect As String) As Data.DataTable
        DataAccessConnection.openConnection()

        Dim command As New OracleClient.OracleCommand
        Dim table As New Data.DataTable

        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT " & stringSelect & " FROM " & obj.getTableName & " WHERE id = " & obj.getId & " AND end_date IS NULL"

        table.Load(command.ExecuteReader)
        DataAccessConnection.closeConnection()

        Return table
    End Function

    Public Sub disabled(ByRef resource As Resource)
        DataAccessConnection.openConnection()
        Dim command As New OracleClient.OracleCommand

        Dim query As String = ""

        query = "UPDATE " & resource.getTableName & ""
        query &= " SET end_date = '" & Today.Date & "'"
        query &= " WHERE id = " & resource.idProperty & ""

        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = query

        command.ExecuteNonQuery()

        DataAccessConnection.closeConnection()
    End Sub
End Module
